package com.guoxue.auth;
/*  
 * @(#)CommonSql.java  2011-9-5  
 *   
 * Copyright 2011 Bianjing,All rights reserved.  
 */    
  
import java.sql.Connection;  
import java.sql.DatabaseMetaData;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement;  
import java.util.List;  
  
import javax.naming.Context;  
import javax.naming.InitialContext;  
import javax.sql.DataSource;  
  
/** 
 * 用于JDBC操作数据库的共通类 
 *  
 * @author Bianjing 
 * @version 1.0.0 2011-9-5 
 */  
public class CommonSql {  
    /** 数据源 */  
    private DataSource dataSource;  
  
    /** 数据库连接对象 */  
    private Connection connection;  
  
    /** 数据库操作对象 */  
    private PreparedStatement ps;  
  
    /** 数据库操作对象 */  
    private Statement statement;  
  
    /** 返回的数据结果集对象 */  
    private ResultSet rs;  
  
    /** 是否自动提交事务，默认为true，如果该值为false则需要手动提交事务 */  
    private boolean autoCommit = true;  
  
    /** 数据库连接是否已经打开 */  
    private boolean openConnection;  
  
    /** JNDI名称 */  
    private String jndiName;  
  
    /** 数据库驱动 */  
    private String driver;  
  
    /** 数据库访问地址 */  
    private String url;  
  
    /** 用户名 */  
    private String user;  
  
    /** 密码 */  
    private String pwd;  
  
    public CommonSql() {  
          
    }  
  
    public CommonSql(DataSource dataSource) {  
        this.dataSource = dataSource;  
    }  
  
    public CommonSql(String jndiName) {  
        this.jndiName = jndiName;  
    }  
  
    public CommonSql(String driver, String url, String user, String pwd) {  
        this.driver = driver;  
        this.url = url;  
        this.user = user;  
        this.pwd = pwd;  
    }  
  
    /** 
     * 打开数据库连接并创建数据库连接对象<br/> 
     * 支持通过ICO注入数据源、数据库驱动、数据库驱动、JNDI名称、数据库访问地址和用户名、密码 
     *  
     * @return boolean true:连接成功，false:连接失败 
     */  
    public boolean openConnection() {  
        /** 
         * 通过数据源来获取数据库连接对象 
         */  
        if (dataSource != null) {  
            try {  
                connection = dataSource.getConnection();  
                // 数据库连接已经打开  
                openConnection = true;  
            } catch (SQLException e) {  
                closeAll();  
                // 所有的"System.out.println"都可以替换为"logger.error"  
                System.out.println("从数据源获取数据库连接失败！");  
                throw new RuntimeException(e);  
            }  
  
            return openConnection;  
        }  
        /** 
         * 通过JNDI来获取数据库连接对象 
         */  
        if (jndiName != null) {  
            try {  
                Context initContext = new InitialContext();  
                dataSource = (DataSource) initContext.lookup(jndiName);  
                connection = dataSource.getConnection();  
                // 数据库连接已经打开  
                openConnection = true;  
            } catch (Exception e) {  
                closeAll();  
                System.out.println("从JNDI获取数据库连接失败！");  
                throw new RuntimeException(e);  
            }  
  
            return openConnection;  
        }  
        /** 
         * 通过数据库驱动、数据库访问地址、用户名、密码来获取数据库连接对象 
         */  
        try {  
            Class.forName(driver);  
            connection = DriverManager.getConnection(url, user, pwd);  
            // 数据库连接已经打开  
            openConnection = true;  
        } catch (Exception e) {  
            closeAll();  
            System.out.println("数据库连接失败！");  
            throw new RuntimeException(e);  
        }  
  
        return openConnection;  
    }  
  
    /** 
     * 执行数据库的更新操作 
     *  
     * @param sql 
     *            要执行的SQL语句 
     * @return boolean true:执行成功，false:执行失败 
     */  
    public boolean execUpdate(String sql, Object... args) {  
        boolean isPassed = false;  
        // 判断连接数据库是否成功  
        if (openConnection) {  
            try {  
                ps = connection.prepareStatement(sql);  
                // 设置参数  
                if (args != null && args.length > 0) {  
                    for (int i = 0; i < args.length; i++) {  
                        ps.setObject(i + 1, args[i]);  
                    }  
                }  
                ps.executeUpdate();  
  
                isPassed = true;  
            } catch (SQLException e) {  
                try {  
                    if (autoCommit) {  
                        connection.rollback();  
                    }  
                } catch (SQLException e1) {  
                    throw new RuntimeException(e1);  
                }  
                System.out.println("SQL:" + sql);  
                throw new RuntimeException(e);  
            } finally {  
                if (autoCommit) {  
                    closeAll();  
                }  
            }  
        } else {  
            System.out.println("数据库连接对象没有打开！");  
        }  
  
        return isPassed;  
    }  
      
    /** 
     * 执行数据库的更新操作 
     *  
     * @param sql 
     *            要执行的SQL语句 
     * @return boolean true:执行成功，false:执行失败 
     */  
    public boolean execUpdate(String sql, List<?> args) {  
        return execUpdate(sql, args.toArray());  
    }  
  
    /** 
     * 执行批量更新数据库操作 
     *  
     * @param sql 
     *            要执行的SQL语句的字符串数组 
     * @return boolean true:执行成功，false:执行失败 
     */  
    public boolean execUpdate(Object[] sql) {  
        boolean flag = false;  
        // 判断连接数据库是否成功  
        if (openConnection) {  
            try {  
                statement = connection.createStatement();  
                for (int i = 0; i < sql.length; i++) {  
                    statement.addBatch((String) sql[i]);  
                }  
                statement.executeBatch();  
  
                flag = true;  
            } catch (SQLException e) {  
                try {  
                    if (autoCommit) {  
                        connection.rollback();  
                    }  
                } catch (SQLException e1) {  
                    throw new RuntimeException(e1);  
                }  
                for (int i = 0; i < sql.length; i++) {  
                    System.out.println("SQL " + (i + 1) + ":" + sql[i]);  
                }  
                throw new RuntimeException(e);  
            } finally {  
                if (autoCommit) {  
                    closeAll();  
                }  
            }  
        } else {  
            System.out.println("数据库连接对象没有打开！");  
        }  
  
        return flag;  
    }  
  
    /** 
     * 执行批量更新数据库操作 
     *  
     * @param sql 
     *            要执行的SQL语句的集合 
     * @return boolean true:执行成功，false:执行失败 
     */  
    public boolean execUpdate(List<?> sql) {  
        return execUpdate(sql.toArray());  
    }  
  
    /** 
     * 执行数据库查询操作 
     *  
     * @param sql 
     *            要执行的SQL语句 
     * @param args 
     *            查询参数列表 
     * @return ResultSet 返回查询的结果集对象 
     */  
    public ResultSet execQuery(String sql, Object... args) {  
        rs = null;  
        // 判断连接数据库是否成功  
        if (openConnection) {  
            try {  
                ps = connection.prepareStatement(sql);  
                // 设置参数  
                if (args != null && args.length > 0) {  
                    for (int i = 0; i < args.length; i++) {  
                        ps.setObject(i + 1, args[i]);  
                    }  
                }  
                  
                rs = ps.executeQuery();  
            } catch (SQLException e) {  
                if (autoCommit) {  
                    closeAll();  
                }  
                System.out.println("SQL:" + sql);  
                throw new RuntimeException(e);  
            }  
        } else {  
            System.out.println("数据库连接对象没有打开！");  
        }  
  
        return rs;  
    }  
  
    /** 
     * 执行数据库查询操作 
     *  
     * @param sql 
     *            要执行的SQL语句 
     * @param args 
     *            查询参数列表 
     * @return ResultSet 返回查询的结果集对象 
     */  
    public ResultSet execQuery(String sql, List<?> args) {  
        return execQuery(sql, args.toArray());  
    }  
  
    /** 
     * 根据标准SQL查询数据库，返回一个int值 
     *  
     * @param sql 
     *            要执行的SQL语句 
     * @param args 
     *            查询参数列表 
     * @return int值，如果出错则返回-1 
     */  
    public int findForInt(String sql, Object... args) {  
        ResultSet rs = execQuery(sql, args);  
        int count = -1;  
        try {  
            if (rs != null && rs.next()) {  
                count = rs.getInt(1);  
            }  
        } catch (SQLException e) {  
            throw new RuntimeException(e);  
        } finally {  
            if (autoCommit) {  
                closeAll();  
            }  
        }  
  
        return count;  
    }  
      
    /** 
     * 根据标准SQL查询数据库，返回一个int值 
     *  
     * @param sql 
     *            要执行的SQL语句 
     * @param args 
     *            查询参数列表 
     * @return int值，如果出错则返回-1 
     */  
    public int findForInt(String sql, List<?> args) {  
        return findForInt(sql, args.toArray());  
    }  
  
    /** 
     * 关闭所有数据库连接对象 
     */  
    public void closeAll() {  
        if (rs != null || ps != null || statement != null || connection != null) {  
            try {  
                if (rs != null) {  
                    rs.close();  
                }  
            } catch (SQLException e) {  
                throw new RuntimeException(e);  
            } finally {  
                rs = null;  
                if (ps != null || statement != null || connection != null) {  
                    try {  
                        if (ps != null && !ps.isClosed()) {  
                            ps.close();  
                        }  
                    } catch (SQLException e) {  
                        throw new RuntimeException(e);  
                    } finally {  
                        ps = null;  
                        if (statement != null || connection != null) {  
                            try {  
                                if (statement != null && !statement.isClosed()) {  
                                    statement.close();  
                                }  
                            } catch (SQLException e) {  
                                throw new RuntimeException(e);  
                            } finally {  
                                statement = null;  
                                try {  
                                    if (connection != null  
                                            && !connection.isClosed()) {  
                                        connection.close();  
                                    }  
                                } catch (SQLException e) {  
                                    throw new RuntimeException(e);  
                                } finally {  
                                    connection = null;  
                                }  
                            }  
                        }  
                    }  
                }  
            }  
        }  
    }  
  
    /** 
     * 提交事务并关闭数据库连接 
     */  
    public void commit() {  
        try {  
            if (!autoCommit) {  
                connection.commit();  
            }  
        } catch (SQLException e) {  
            throw new RuntimeException(e);  
        } finally {  
            autoCommit = true;  
            closeAll();  
        }  
    }  
  
    /** 
     * 回滚事务并关闭数据库连接 
     */  
    public void rollback() {  
        try {  
            if (!autoCommit) {  
                connection.rollback();  
            }  
        } catch (SQLException e) {  
            throw new RuntimeException(e);  
        } finally {  
            autoCommit = true;  
            closeAll();  
        }  
    }  
  
    public DataSource getDataSource() {  
        return dataSource;  
    }  
  
    public void setDataSource(DataSource dataSource) {  
        this.dataSource = dataSource;  
    }  
  
    public String getDriver() {  
        return driver;  
    }  
  
    public void setDriver(String driver) {  
        this.driver = driver;  
    }  
  
    public String getUrl() {  
        return url;  
    }  
  
    public void setUrl(String url) {  
        this.url = url;  
    }  
  
    public boolean getAutoCommit() {  
        return autoCommit;  
    }  
  
    public void setAutoCommit(boolean autoCommit) {  
        try {  
            connection.setAutoCommit(autoCommit);  
        } catch (SQLException e) {  
            closeAll();  
            throw new RuntimeException(e);  
        }  
        this.autoCommit = autoCommit;  
    }  
  
    public boolean getOpenConnection() {  
        return openConnection;  
    }  
  
    public String getJndiName() {  
        return jndiName;  
    }  
  
    public void setJndiName(String jndiName) {  
        this.jndiName = jndiName;  
    }  
  
    public String getUser() {  
        return user;  
    }  
  
    public void setUser(String user) {  
        this.user = user;  
    }  
  
    public String getPwd() {  
        return pwd;  
    }  
  
    public void setPwd(String pwd) {  
        this.pwd = pwd;  
    }  
  
    public Connection getConnection() {  
        return connection;  
    }  
  
    /** 
     * 测试数据库连接是否成功 
     *  
     * @param args 
     * @throws SQLException 
     */  
    public static void main(String[] args) throws SQLException {  
        String driver = "com.mysql.jdbc.Driver";  
        String url = "jdbc:mysql://localhost:3306/copyguoxue?useUnicode=true&amp;characterEncoding=UTF-8";  
        String user = "root";  
        String pwd = "root";  
        CommonSql commonSql = new CommonSql(driver, url, user, pwd);  
        if (commonSql.openConnection()) {  
            System.out.println("数据库连接成功！");  
            DatabaseMetaData dbMetaData = commonSql.getConnection()  
                    .getMetaData();  
            System.out  
                    .print("当前连接的数据库是:" + dbMetaData.getDatabaseProductName());  
            System.out.println(" " + dbMetaData.getDatabaseProductVersion());  
        } else {  
            System.out.println("数据库连接失败！");  
        }  
        commonSql.closeAll();  
    }  
} 